{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a script that will take in the SphygmoCor CVMS files, parse them and send them to the MySQL server."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import getpass\n",
    "import sqlalchemy\n",
    "import mysql.connector\n",
    "import yaml\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setup Connection to DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Please enter SQL UserID: jbrown\n",
      "Enter SQL Password········\n"
     ]
    }
   ],
   "source": [
    "Username = input(\"Please enter SQL UserID: \")\n",
    "Password = getpass.getpass('Enter SQL Password')\n",
    "con = \"mysql+pymysql://%s:%s@18.42.4.88/bav_bch\" %(Username, Password)\n",
    "engine = sqlalchemy.create_engine(con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def config():\n",
    "    '''\n",
    "    Parses the config and returns it as a dictionary\n",
    "    '''\n",
    "    with open(\"/home/brownjy/config.yaml\") as f:\n",
    "        return yaml.load(f.read())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def database():\n",
    "    '''\n",
    "    Opens a connection to the database and returns the connection object.\n",
    "    '''\n",
    "    conf = config()\n",
    "    return mysql.connector.connect(user=Username, password=Password,\n",
    "                              host=conf[\"dbhost\"],\n",
    "                              database=conf[\"dbtable\"],\n",
    "                              port=conf[\"dbport\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Define Parsing Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def CVMS_Import(File, path):\n",
    "    \"\"\"This will parse the file and output a data frame of pressures waveforms\"\"\"\n",
    "    \n",
    "    # Read in file change column names and drop all na values for waveform data\n",
    "    data = pd.read_csv((path + File), sep= '\\t', header=2)\n",
    "    data.columns = ['periphSignal', 'centralSignal', 'periph_pulse','central_pulse']\n",
    "    data = data.dropna()\n",
    "    data.drop(['periphSignal', 'centralSignal'], axis=1, inplace = True)\n",
    "    \n",
    "    # Import \"Header Data\" and use to both save and add time column\n",
    "    HeaderValues = ['System_ID', 'Database_ID', 'Patient_Number', 'surname',\n",
    "        'first_name', 'sex', 'date_of_birth', 'patient_id', 'patient_code',\n",
    "        'patient_notes', 'sp', 'dp', 'mp', 'data_rev', 'datetime', 'age',\n",
    "        'medication', 'notes', 'operator', 'interpretation', 'height',\n",
    "        'weight', 'body_mass_index', 'sample_rate', 'simulation',\n",
    "        'tonometer_serial_number', 'sub_type', 'inconclusive',\n",
    "        'reference_age', 'ppampratio', 'p_max_dpdt', 'ed', 'calced',\n",
    "        'quality_ed', 'p_qc_ph', 'p_qc_phv', 'p_qc_plv', 'p_qc_dv',\n",
    "        'p_qc_sdev', 'operator_index', 'p_sp', 'p_dp', 'p_meanp', 'p_t1',\n",
    "        'p_t2', 'p_ai', 'p_calct1', 'p_calct2', 'p_esp', 'p_p1', 'p_p2',\n",
    "        'p_t1ed', 'p_t2ed', 'p_quality_t1', 'p_quality_t2', 'c_ap',\n",
    "        'c_ap_hr75', 'c_mps', 'c_mpd', 'c_tti', 'c_dti', 'c_svi', 'c_al',\n",
    "        'c_ati', 'hr', 'c_period', 'c_dd', 'c_ed_period', 'c_dd_period',\n",
    "        'c_ph', 'c_agph', 'c_agph_hr75', 'c_p1_height', 'c_t1r', 'c_sp',\n",
    "        'c_dp', 'c_meanp', 'c_t1', 'c_t2', 'c_ai', 'c_calct1', 'c_calct2',\n",
    "        'c_esp', 'c_p1', 'c_p2', 'c_t1ed', 'c_t2ed', 'c_quality_t1',\n",
    "        'c_quality_t2']\n",
    "    \n",
    "    data_head = pd.read_csv((path + File), sep='\\t', nrows=1 ,header = 0, names = HeaderValues)\n",
    "    data_head.insert(0,'Subject_ID', File[:-4])\n",
    "    \n",
    "    \n",
    "    #Send Data to MySQL\n",
    "    data_head.to_sql('cvmsdata', engine, if_exists=\"append\", index = False, index_label=None)\n",
    "    \n",
    "    return (data, data_head)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def CleanWaveformData(data, data_head):\n",
    "    \n",
    "    # Add time column to data\n",
    "    time, step = np.linspace(0, data_head['c_period'][0]/1000, \n",
    "                        num = len(data),endpoint=True, retstep=True)\n",
    "    #Add time to dataframe\n",
    "    data['time'] = time\n",
    "    \n",
    "    #Add Column for measurement ID\n",
    "    SQL_id = GrabMeasurementID(str(data_head['Subject_ID'][0]))\n",
    "    data['measurement_id'] = SQL_id[0]\n",
    "    \n",
    "    #Rearrange Column for MySQL Input\n",
    "    data = data[['measurement_id', 'periph_pulse', 'central_pulse', 'time']]\n",
    "    \n",
    "    return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def GrabMeasurementID(PatientID):\n",
    "    \n",
    "    cnx = database()\n",
    "    cursor = cnx.cursor()\n",
    "    query = ('''\n",
    "    SELECT id from cvmsdata where Subject_ID = '%s'\n",
    "    ''' % PatientID)\n",
    "\n",
    "    cursor.execute(query)\n",
    "    output = cursor.fetchone()\n",
    "    return output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "FileList = os.listdir('/home/brownjy/scratch/PWA_BAV/')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for sample in FileList:\n",
    "    try:\n",
    "        data, data_head = CVMS_Import(sample,'/home/brownjy/scratch/PWA_BAV/')\n",
    "        data = CleanWaveformData(data, data_head)\n",
    "        data.to_sql('waveform', engine, if_exists=\"append\", index = False, index_label=None)\n",
    "    except:\n",
    "        print('Duplicate!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'BAV_112b.txt'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "234"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(FileList)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
